﻿CREATE TABLE [fb].[CurrencyExchange] (
    [CurrencyExchangeId] INT             IDENTITY (1, 1) NOT NULL,
    [ExchangeType]       INT             NOT NULL,
    [Rate]               DECIMAL (18, 2) NOT NULL,
    [BuyTransactionId]   INT             NOT NULL,
    [SellTransactionId]  INT             NOT NULL,
    [BaseCurrencyCode]   NVARCHAR (3)    NOT NULL,
    [QuoteCurrencyCode]  NVARCHAR (3)    NOT NULL,
    CONSTRAINT [PK_fb.CurrencyExchange] PRIMARY KEY CLUSTERED ([CurrencyExchangeId] ASC),
    CONSTRAINT [FK_fb.CurrencyExchange_fb.Currency_BaseCurrencyCode] FOREIGN KEY ([BaseCurrencyCode]) REFERENCES [fb].[Currency] ([Code]),
    CONSTRAINT [FK_fb.CurrencyExchange_fb.Currency_QuoteCurrencyCode] FOREIGN KEY ([QuoteCurrencyCode]) REFERENCES [fb].[Currency] ([Code]),
    CONSTRAINT [FK_fb.CurrencyExchange_fb.Transaction_BuyTransactionId] FOREIGN KEY ([BuyTransactionId]) REFERENCES [fb].[Transaction] ([TransactionId]),
    CONSTRAINT [FK_fb.CurrencyExchange_fb.Transaction_SellTransactionId] FOREIGN KEY ([SellTransactionId]) REFERENCES [fb].[Transaction] ([TransactionId])
);


GO
CREATE NONCLUSTERED INDEX [IX_QuoteCurrencyCode]
    ON [fb].[CurrencyExchange]([QuoteCurrencyCode] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_BaseCurrencyCode]
    ON [fb].[CurrencyExchange]([BaseCurrencyCode] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_SellTransactionId]
    ON [fb].[CurrencyExchange]([SellTransactionId] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_BuyTransactionId]
    ON [fb].[CurrencyExchange]([BuyTransactionId] ASC);

